﻿CREATE PROCEDURE [maint].[BackupLogToDevice]
@DbName [sysname], @DeviceName [sysname], @BackupSetName [sysname], @BackupSetExpireDate DATETIME, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int;
	SET @RC = 0;

	DECLARE @DeviceDoP int;
	SET @DeviceDoP = maint.GetDeviceDoP(@DbName);

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID);
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + '''';
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + '''';
			PRINT N'	@BackupSetName = N''' + ISNULL(@BackupSetName,N'NULL') + '''';
			PRINT N'	@BackupSetExpireDate = N''' + ISNULL(CAST(@BackupSetExpireDate AS nvarchar),N'NULL') + '''';
			PRINT N'	@DeviceDoP = ' + CAST(@DeviceDoP AS nvarchar);
		END

	DECLARE @DeviceListExpr nvarchar(max) = maint.GetDeviceListExpression(@DeviceName, @DeviceDoP);

	DECLARE @LogBackupStmnt nvarchar(1024)
	SET @LogBackupStmnt = N'
BACKUP LOG [' + @DbName + '] TO ' + @DeviceListExpr + ' WITH 
DESCRIPTION = ''LOG Backup by DBA''
,EXPIREDATE = ''' + CONVERT(nchar(23),@BackupSetExpireDate,126) + '''
,NOINIT
,MEDIADESCRIPTION = ''SQL Backup Device''
,MEDIANAME = ''' + @DeviceName + '''
,NAME = ''' + @BackupSetName + '''
,COMPRESSION
,STATS = 10';

	IF(@OutputDiagnostics = 1)
		PRINT N'	@LogBackupStmnt = ''' + ISNULL(@LogBackupStmnt,N'NULL') + '''';

	EXEC sp_executesql @LogBackupStmnt;

EXIT_PROC:
	RETURN(@RC);
END